﻿
CREATE PROCEDURE [dbo].[SynchronizeState] ( @Rebuild BIT )
AS 
    BEGIN
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

        BEGIN TRANSACTION ;
        BEGIN TRY
            IF ( NOT EXISTS ( SELECT    *
                              FROM      [dbo].[State] )
                 OR @Rebuild = 1
               ) 
                BEGIN
                    DELETE  FROM dbo.[State]
				
-- [Id]=1 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=1)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (1, N'cn', N'cn_ah', N'Anhui', N'安徽', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_ah'
                   , [StateName]=N'Anhui'
                   , [StateLocalName]=N'安徽'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=1


-- [Id]=2 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=2)
   INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (2, N'cn', N'cn_bj', N'Beijing', N'北京', 2, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_bj'
                   , [StateName]=N'Beijing'
                   , [StateLocalName]=N'北京'
                   , [StateType]=2
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=2


-- [Id]=3 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=3)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (3, N'cn', N'cn_cq', N'Chongqing', N'重庆', 2, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_cq'
                   , [StateName]=N'Chongqing'
                   , [StateLocalName]=N'重庆'
                   , [StateType]=2
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=3


-- [Id]=4 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=4)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (4, N'cn', N'cn_fj', N'Fujian', N'福建', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_fj'
                   , [StateName]=N'Fujian'
                   , [StateLocalName]=N'福建'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=4


-- [Id]=5 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=5)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (5, N'cn', N'cn_gs', N'Gansu', N'甘肃', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_gs'
                   , [StateName]=N'Gansu'
                   , [StateLocalName]=N'甘肃'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=5


-- [Id]=6 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=6)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (6, N'cn', N'cn_gd', N'Guangdong', N'广东', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_gd'
                   , [StateName]=N'Guangdong'
                   , [StateLocalName]=N'广东'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=6


-- [Id]=7 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=7)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (7, N'cn', N'cn_gx', N'Guangxi', N'广西', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_gx'
                   , [StateName]=N'Guangxi'
                   , [StateLocalName]=N'广西'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=7


-- [Id]=8 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=8)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (8, N'cn', N'cn_gz', N'GuiZhou', N'贵州', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_gz'
                   , [StateName]=N'GuiZhou'
                   , [StateLocalName]=N'贵州'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=8


-- [Id]=9 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=9)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (9, N'cn', N'cn_ha', N'Hainan', N'海南', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_ha'
                   , [StateName]=N'Hainan'
                   , [StateLocalName]=N'海南'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=9


-- [Id]=10 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=10)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (10, N'cn', N'cn_hb', N'Hebei', N'河北', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_hb'
                   , [StateName]=N'Hebei'
                   , [StateLocalName]=N'河北'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=10


-- [Id]=11 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=11)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (11, N'cn', N'cn_hl', N'Heilongjiang', N'黑龙江', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_hl'
                   , [StateName]=N'Heilongjiang'
                   , [StateLocalName]=N'黑龙江'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=11


-- [Id]=12 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=12)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (12, N'cn', N'cn_hn', N'Henan', N'河南', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_hn'
                   , [StateName]=N'Henan'
                   , [StateLocalName]=N'河南'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=12


-- [Id]=13 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=13)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (13, N'cn', N'cn_hu', N'Hubei', N'湖北', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_hu'
                   , [StateName]=N'Hubei'
                   , [StateLocalName]=N'湖北'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=13


-- [Id]=14 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=14)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (14, N'cn', N'cn_na', N'Hunan', N'湖南', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_na'
                   , [StateName]=N'Hunan'
                   , [StateLocalName]=N'湖南'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=14


-- [Id]=15 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=15)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (15, N'cn', N'cn_mg', N'Inner Mongolia', N'内蒙古', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_mg'
                   , [StateName]=N'Inner Mongolia'
                   , [StateLocalName]=N'内蒙古'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=15


-- [Id]=16 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=16)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (16, N'cn', N'cn_js', N'Jiangsu', N'江苏', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_js'
                   , [StateName]=N'Jiangsu'
                   , [StateLocalName]=N'江苏'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=16


-- [Id]=17 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=17)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (17, N'cn', N'cn_jx', N'Jiangxi', N'江西', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_jx'
                   , [StateName]=N'Jiangxi'
                   , [StateLocalName]=N'江西'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=17


-- [Id]=18 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=18)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (18, N'cn', N'cn_jl', N'Jilin', N'吉林', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_jl'
                   , [StateName]=N'Jilin'
                   , [StateLocalName]=N'吉林'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=18


-- [Id]=19 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=19)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (19, N'cn', N'cn_ln', N'Liaoning', N'辽宁', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_ln'
                   , [StateName]=N'Liaoning'
                   , [StateLocalName]=N'辽宁'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=19


-- [Id]=20 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=20)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (20, N'cn', N'cn_nx', N'Ningxia', N'宁夏', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_nx'
                   , [StateName]=N'Ningxia'
                   , [StateLocalName]=N'宁夏'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=20


-- [Id]=21 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=21)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (21, N'cn', N'cn_qh', N'Qinghai', N'青海', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_qh'
                   , [StateName]=N'Qinghai'
                   , [StateLocalName]=N'青海'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=21


-- [Id]=22 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=22)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
      , [InsertDate]
       , [UpdateDate]
    )
    VALUES (22, N'cn', N'cn_sx', N'Shaanxi', N'山西', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_sx'
                   , [StateName]=N'Shaanxi'
                   , [StateLocalName]=N'山西'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=22


-- [Id]=23 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=23)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (23, N'cn', N'cn_sd', N'Shandong', N'山东', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_sd'
                   , [StateName]=N'Shandong'
                   , [StateLocalName]=N'山东'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=23


-- [Id]=24 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=24)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (24, N'cn', N'cn_sh', N'Shanghai', N'上海', 2, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_sh'
                   , [StateName]=N'Shanghai'
                   , [StateLocalName]=N'上海'
                   , [StateType]=2
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=24


-- [Id]=25 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=25)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (25, N'cn', N'cn_sa', N'Shanxi', N'陕西', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_sa'
                   , [StateName]=N'Shanxi'
                   , [StateLocalName]=N'陕西'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=25


-- [Id]=26 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=26)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (26, N'cn', N'cn_sc', N'Sichuan', N'四川', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_sc'
                   , [StateName]=N'Sichuan'
                   , [StateLocalName]=N'四川'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=26


-- [Id]=27 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=27)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (27, N'cn', N'cn_tj', N'Tianjin', N'天津', 2, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_tj'
                   , [StateName]=N'Tianjin'
                   , [StateLocalName]=N'天津'
                   , [StateType]=2
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=27


-- [Id]=28 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=28)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (28, N'cn', N'cn_xj', N'Xinjiang', N'新疆', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_xj'
                   , [StateName]=N'Xinjiang'
                   , [StateLocalName]=N'新疆'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=28


-- [Id]=29 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=29)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (29, N'cn', N'cn_tb', N'Xizang', N'西藏', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_tb'
                   , [StateName]=N'Xizang'
                   , [StateLocalName]=N'西藏'
                  , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=29


-- [Id]=30 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=30)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (30, N'cn', N'cn_yn', N'Yunnan', N'云南', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_yn'
                   , [StateName]=N'Yunnan'
                   , [StateLocalName]=N'云南'
                  , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=30


-- [Id]=31 ---------------------------------
IF NOT EXISTS (SELECT * FROM dbo.State WHERE [Id]=31)
    INSERT INTO dbo.State
    (
         [Id]
       , [CountryCode]
       , [StateCode]
       , [StateName]
       , [StateLocalName]
       , [StateType]
       , [InsertDate]
       , [UpdateDate]
    )
    VALUES (31, N'cn', N'cn_zj', N'Zhejiang', N'浙江', 1, '2010-06-29 04:56:05.050', '1900-01-01 00:00:00.000')
ELSE 
                UPDATE dbo.State SET 
                     [CountryCode]=N'cn'
                   , [StateCode]=N'cn_zj'
                   , [StateName]=N'Zhejiang'
                   , [StateLocalName]=N'浙江'
                   , [StateType]=1
                   , [InsertDate]='2010-06-29 04:56:05.050'
                   , [UpdateDate]='1900-01-01 00:00:00.000'
                WHERE [Id]=31



                END
            COMMIT ;
        END TRY
        BEGIN CATCH
            ROLLBACK ;
        END CATCH
    END